﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using MySql.Data.MySqlClient;
using static System.IO.File;

namespace Task
{
    public static class DbHandler
    {
        private static string ConnectStr;
        private static string ServiceName;
        private static string Username;
        private static string Password;
        private static string MaterialTableName = "material";
        private static string MaterialTypeTableName = "type";
        private static string MaterialSubTypeTableName = "subtype";
        static string backupFile = "sentencelist.sql";

        public static void Init()
        {
            var reader = new StreamReader("config.cfg");
            var cfg = reader.ReadLine();
            if (cfg != null)
            {
                var config = cfg.Split('|');
                Username = config[0];
                Password = config[1];
                ServiceName = config[2];
            }

            ConnectStr =
                $"Database=sentencelist;server=127.0.0.1;port=3306;user={Username};password={Password};SslMode=none;";
            reader.Close();
            reader.Dispose();

            using (var conn = new MySqlConnection(ConnectStr))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    var process = new Process
                    {
                        StartInfo =
                        {
                            FileName = "cmd.exe",
                            UseShellExecute = false,
                            RedirectStandardInput = true,
                            RedirectStandardOutput = true,
                            RedirectStandardError = true,
                            CreateNoWindow = true
                        }
                    };
                    process.Start();
                    process.StandardInput.WriteLine($"mysql -uroot -p{Password}&exit");
                    process.StandardInput.Flush();
                }
            }
        }

        public static void CreateTable(string tableName)
        {
            var sql = $"create table {tableName}";
            HandleSql(sql);
        }

        public static void HandleSql(string sql)
        {
            using (var coon = new MySqlConnection(ConnectStr))
            {
                coon.Open();
                using (var cmd = new MySqlCommand(sql, coon))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static List<Material> MaterialsReaderSql(string sql)
        {
            var materials = new List<Material>();
            using (var conn = new MySqlConnection(ConnectStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var name = reader.GetString("name");
                        var type = reader.GetString("type");
                        var subtype = reader.GetString("subtype");
                        var content = reader.GetString("content");
                        var date = reader.GetString("date");
                        var id = reader.GetInt32("id");
                        var material = new Material(id, name, type, subtype, content, date);
                        materials.Add(material);
                    }

                    reader.Close();
                }
            }

            return materials;
        }

        public static List<Material> GetAllMaterials()
        {
            var sql = $"select * from {MaterialTableName}" ;
            return MaterialsReaderSql(sql);
        }

        public static List<string> ShowTables()
        {
            var tables = new List<string>();

            const string sql = "show tables";
            using (var coon = new MySqlConnection(ConnectStr))
            {
                using (var cmd = new MySqlCommand(sql, coon))
                {
                    coon.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }

                    return tables;
                }
            }
        }

        public static List<string> GetAllType()
        {
            var types = new List<string>();

            var sql = $"select * from {MaterialTypeTableName}" ;
            using (var conn = new MySqlConnection(ConnectStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var type = reader.GetString("name");
                        types.Add(type);
                    }

                    reader.Close();
                }
            }

            return types;
        }

        public static Dictionary<string, string> GetAllSubType()
        {
            var subTypes = new Dictionary<string, string>();

            var sql = $"select * from {MaterialSubTypeTableName}";
            using (var conn = new MySqlConnection(ConnectStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var subtype = reader.GetString("name");
                        var type = reader.GetString("type");
                        subTypes.Add(subtype, type);
                    }

                    reader.Close();
                }
            }

            return subTypes;
        }

        public static void AddMaterial(Material material)
        {
            var date = DateTime.Now.ToString();

            var sql =
                $"insert into {MaterialTableName} set name='{material.Name}',type='{material.Type}',subtype='{material.SubType}',content='{material.Content}',date='{date}'";

            HandleSql(sql);
        }

        public static void AddType(string typeName)
        {
            var sql = $"insert into {MaterialTypeTableName} set name='{typeName}'";
            HandleSql(sql);
        }

        public static void AddSubType(string subTypeName, string type)
        {
            var sql = $"insert into {MaterialSubTypeTableName} set name='{subTypeName}',type='{type}'";
            HandleSql(sql);
        }

        public static void Backup()
        {
            var file = new FileInfo(backupFile);
            if (!file.Exists)
            {
                Create(backupFile);
            }

            using (var conn = new MySqlConnection(ConnectStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand())
                {
                    using (var mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        mb.ExportToFile(backupFile);
                    }
                }
            }
        }

        public static void Restore()
        {
            using (var conn = new MySqlConnection(ConnectStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand())
                {
                    using (var mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        mb.ImportFromFile(backupFile);
                    }
                }
            }
        }

        public static List<Material> Search(string containContent)
        {
            var sql = $"select * from {MaterialTableName} where content like '%{containContent}%'";
            return MaterialsReaderSql(sql);
        }

        public static List<Material> SubTypeSearch(string typeName, string subtypeName)
        {
            var sql = $"select * from {MaterialTableName} where (type = '{typeName}' and subtype = '{subtypeName}')";
            return MaterialsReaderSql(sql);
        }

        public static List<Material> TypeSearch(string typeName)
        {
            var sql = $"select * from {MaterialTableName} where type = '{typeName}'";
            return MaterialsReaderSql(sql);
        }

        public static void DeleteMaterial(Material material)
        {
            var sql = $"delete from {MaterialTableName} where id='{material.Id}'";
            HandleSql(sql);
        }

        public static void UpdateMaterial(Material material)
        {
            var sql =
                $"update {MaterialTableName} set name='{material.Name}',type='{material.Type}',subtype='{material.SubType}',content='{material.Content}',date='{material.Date}' where id='{material.Id}' ";

            HandleSql(sql);
        }

        public static List<Material> LengthSearch(int min, int max)
        {
            var sql = $"select * from {MaterialTableName} where length(content)>={min} and length(content)<{max}";
            return MaterialsReaderSql(sql);
        }

        public static List<Material> DateSearch(DateTime date)
        {
            var dateStr = date.ToString();
            dateStr = dateStr.Replace('/', '-');
            dateStr = dateStr.Substring(0, 10);

            var sql = $"select * from {MaterialTableName} where date like '%{dateStr}%'";
            return MaterialsReaderSql(sql);
        }

        public static List<Material> TitleSearch(string name)
        {
            var sql = $"select * from {MaterialTableName} where name like '%{name}%'";
            return MaterialsReaderSql(sql);
        }
    }
}